package com.geping.etl.utils.common.commonsql;

import com.geping.etl.common.entity.Logs;
import com.geping.etl.common.entity.Sys_UserAndOrgDepartment;
import com.geping.etl.common.service.LogsService;
import com.geping.etl.utils.common.CommonStringutil;
import com.geping.etl.utils.entity.ResponseResult;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.*;
import javax.servlet.http.HttpServletRequest;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

/**
 * @Author: wangzd
 * @Date: 2020/7/1
 */
@Component
@Transactional
public class CommonSqlUtil<T> {

    private Logger logger=Logger.getLogger(CommonSqlUtil.class);

    @PersistenceContext
    private EntityManager entityManager;

    @Autowired
    private LogsService logsService;
    @Autowired
    private HttpServletRequest request;

    /**
     * 根据条件查询
     *
     * @param t         查询实体
     * @param paramList 参数
     * @return List
     */
    @Transactional(readOnly = true)
    public <T> List<T> queryAll(T t, List<ParamUtil> paramList) {
        Class<?> aClass = t.getClass();
        String className = aClass.getSimpleName();
        StringBuffer sql = new StringBuffer();
        sql.append("from " + className);
        //拼接where条件
        paramList = this.setWhereSql(paramList, sql);
        Query query = entityManager.createQuery(String.valueOf(sql));
        //赋值
        this.setParameterSql(paramList,query);
        List<T> resultList = query.getResultList();
        this.flushAndClear();
        return resultList;
    }

    /**
     * 查询数量
     * @param t 实体类
     * @param paramList 条件
     * @return
     */
    @Transactional(readOnly = true)
    public Long queryCount(T t,List<ParamUtil> paramList){
        String tableName = t.getClass().getAnnotation(Table.class).name();
        StringBuffer countSql = new StringBuffer();
        countSql.append("select count(*) from " + tableName);
        //拼接where条件
        paramList = this.setWhereSql(paramList, countSql);
        Query countQuery = entityManager.createNativeQuery(String.valueOf(countSql));
        //赋值
        this.setParameterSql(paramList,countQuery);
        Long totalCount = Long.valueOf(String.valueOf(countQuery.getSingleResult()));
        return totalCount;
    }

    /**
     * 分页 不查询总数
     * @param t
     * @param pageRequest
     * @param paramList
     * @param <T>
     * @return
     */
    public <T> List<T> pageSingle(T t, PageRequest pageRequest, List<ParamUtil> paramList){
        Class<?> aClass = t.getClass();
        String className = aClass.getSimpleName();
        StringBuffer sql = new StringBuffer();
        sql.append("from " + className);
        paramList = this.setWhereSql(paramList, sql);
        Query query = entityManager.createQuery(String.valueOf(sql));
        query.setFirstResult(pageRequest.getOffset()).setMaxResults(pageRequest.getPageSize());
        this.setParameterSql(paramList,query);
        List<T> resultList = query.getResultList();
        this.flushAndClear();
        return resultList;
    }

    /**
     * 查询并分页 查询总数
     *
     * @param t           实体类
     * @param paramList   条件
     * @param pageRequest 分页
     * @return ResponseResult
     */
    @Transactional(readOnly = true)
    public <T> ResponseResult<T> pageAll(T t, PageRequest pageRequest, List<ParamUtil> paramList) {
        Class<?> aClass = t.getClass();
        String tableName = aClass.getAnnotation(Table.class).name();
        String className = aClass.getSimpleName();
        StringBuffer countSql = new StringBuffer();
        StringBuffer sql = new StringBuffer();
        countSql.append("select count(1) from " + tableName);
        sql.append("from " + className);
        String firstParam="";
        int count=1;
        if (paramList != null && paramList.size() > 0) {
            countSql.append(" where 1=1 ");
            sql.append(" where 1=1 ");
            for (ParamUtil paramUtil : paramList) {
                String field = paramUtil.getField();
                if ("sort".equals(paramUtil.getConnector())){
                    sql.append(paramUtil.where());
                }else {
                    if (field.equals(firstParam)){
                        countSql.append(paramUtil.where(count));
                        sql.append(paramUtil.where(count));
                        paramUtil.setField(field+count);
                        count++;
                    }else {
                        countSql.append(paramUtil.where());
                        sql.append(paramUtil.where());
                    }
                }
                firstParam=field;
            }
        }
        Query countQuery = entityManager.createNativeQuery(String.valueOf(countSql));
        Query query = entityManager.createQuery(String.valueOf(sql));
        query.setFirstResult(pageRequest.getOffset()).setMaxResults(pageRequest.getPageSize());
        if (paramList != null && paramList.size() > 0) {
            for (ParamUtil paramUtil : paramList) {
                if ("sort".equals(paramUtil.getConnector())){
                    continue;
                }
                countQuery.setParameter(paramUtil.getField(), paramUtil.getValue());
                query.setParameter(paramUtil.getField(), paramUtil.getValue());
            }
        }
        Long totalCount = Long.valueOf(String.valueOf(countQuery.getSingleResult()));
        List<T> resultList = query.getResultList();
        this.flushAndClear();
        return ResponseResult.success(totalCount, resultList);
    }

    /**
     * 新增或修改
     * @param entity 实体类
     * @param <S>
     * @return S
     */
    public <S extends T> S addOrUpdate(S entity){
        S merge = this.entityManager.merge(entity);
        this.flushAndClear();
        return merge;
    }

    /**
     * 新增数据
     *
     * @param entity 实体类
     * @param <S>
     * @return S
     */
    public <S extends T> S add(S entity) {
        this.entityManager.persist(entity);
        this.flushAndClear();
        return entity;
    }

    /**
     * 新增多条数据
     * 批处理->使用时必须在配置文件中添加相应配置
     * @param entities 实体类的集合
     * @param <S>
     * @return
     */
    public <S extends T> int addAll(Iterable<S> entities) {
        int BATCH_SIZE = 1000;
        int index = 0;
        if (entities == null) {
            return 0;
        } else {
            Iterator<S> var3 = entities.iterator();
            while (var3.hasNext()) {
                S entity = var3.next();
                this.entityManager.persist(entity);
                index++;
                if (index % BATCH_SIZE == 0) {
                    entityManager.flush();
                    entityManager.clear();
                }
            }
            if (index % BATCH_SIZE != 0) {
                entityManager.flush();
                entityManager.clear();
            }
            return index;
        }
    }

    /**
     * 修改数据
     *
     * @param entity     赋值要修改的字段
     * @param paramUtils where条件
     * @param setNull    需要赋值为空的字段
     * @param <S>
     * @return
     */
    public <S extends T> int update(S entity, List<ParamUtil> paramUtils, String... setNull) {
        Map<String, Object> setMap = this.setField(entity);
        if (setNull != null && setNull.length > 0) {
            for (String nullValue : setNull) {
                setMap.put(nullValue, null);
            }
        }
        if (setMap == null || setMap.size() <= 0) {
            return 0;
        }
        String className = entity.getClass().getSimpleName();
        StringBuffer sql = new StringBuffer();
        sql.append("update " + className + " set ");
        Object primaryKey = setMap.get("@Id");
        Object primaryValue = setMap.get(primaryKey);
        //移除@Id值,主键值不能被赋值为null并便于后续遍历
        setMap.remove("@Id");
        setMap.remove(primaryKey);
        setMap.forEach((setField, value) -> {
            sql.append(setField + "='" + value + "',");
        });
        sql.delete(sql.length() - 1, sql.length());
        Query query;
        if (paramUtils == null || paramUtils.size() <= 0) {
            //条件为空,根据setEntity中id进行修改
            if (primaryKey == null || primaryValue == null) {
                logger.info("ERROR",
                        new NoSuchFieldException("paramUtils is null,must be have primaryKey OR primaryValue"));
                return 0;
            }
            sql.append(" where " + primaryKey + "=" + primaryValue);
            query = this.entityManager.createQuery(String.valueOf(sql));
        } else {
            paramUtils = this.setWhereSql(paramUtils, sql);
            query = this.entityManager.createQuery(String.valueOf(sql));
            this.setParameterSql(paramUtils,query);
        }
        int i = query.executeUpdate();
        this.flushAndClear();
        return i;
    }

    /**
     * 删除数据
     * @param entity
     * @param paramUtils List<ParamUtil> paramUtils
     * @param <S>
     * @return
     */
    public <S extends T> int delete(S entity, List<ParamUtil> paramUtils) {
        StringBuffer sql = new StringBuffer();
        String className = entity.getClass().getSimpleName();
        sql.append("delete from " + className);
        Query query = null;
        if (paramUtils != null && paramUtils.size() > 0) {
            query=this.deletePart(sql,paramUtils);
        } else {
            return 0;
        }
        int i = query.executeUpdate();
        return i;
    }

    /**
     * 删除数据
     * @param entity
     * @param paramUtils List<ParamUtil> paramUtils
     * @param flag 删除所有时为了防止操作失误-true:删除,false:不删除
     * @param <S>
     * @return
     */
    public <S extends T> int delete(S entity, List<ParamUtil> paramUtils,boolean flag) {
        StringBuffer sql = new StringBuffer();
        String className = entity.getClass().getSimpleName();
        sql.append("delete from " + className);
        Query query = null;
        if (paramUtils != null && paramUtils.size() > 0) {
            query=this.deletePart(sql,paramUtils);
        } else {
            if (!flag){
                return 0;
            }
            query = this.entityManager.createQuery(String.valueOf(sql));
        }
        int i = query.executeUpdate();
        return i;
    }

    /**
     * oracle调用存储过程
     * @param procedure 存储过程名称
     * @param properties 存储过程参数
     */
    public void callProcedure(String procedure,String... properties){
        StringBuffer call=new StringBuffer();
        call.append("call "+procedure+"(");//)}"
        if (properties!=null && properties.length>0){
            for (int i = 0; i < properties.length; i++) {
                call.append("'"+properties[i]+"',");
            }
            call.delete(call.length()-1,call.length());
        }
        call.append(")");
        Query query = entityManager.createNativeQuery(String.valueOf(call));
        query.executeUpdate();
    }
    /**
     * sqlserver调用存储过程
     * @param procedure 存储过程名称
     * @param properties 存储过程参数
     */
    public void execProcedure(String procedure,String... properties){
        StringBuffer call=new StringBuffer();
        call.append("exec "+procedure+" ");//)}"
        if (properties!=null && properties.length>0){
            for (int i = 0; i < properties.length; i++) {
                call.append("'"+properties[i]+"',");
            }
            call.delete(call.length()-1,call.length());
        }
        Query query = entityManager.createNativeQuery(String.valueOf(call));
        query.executeUpdate();
    }

//    /**
//     * 扩展-上报完成后操作数据库
//     * @param t
//     * @param hisT
//     * @param paramUtils
//     * @param uuid
//     * @return
//     */
//    public int handleReport(T t, T hisT, String uuid, List<ParamUtil> paramUtils,IHisReport hisReport){
//        //保存到IHisReport
//        this.add((T) hisReport);
//        //处理数据
//        List<ParamUtil> newParamList=new ArrayList<>();
//        List<ParamUtil> delParamList=new ArrayList<>();
//        newParamList.addAll(paramUtils);
//        delParamList.addAll(paramUtils);
//        String tableName = t.getClass().getAnnotation(Table.class).name();
//        String HisTableName = hisT.getClass().getAnnotation(Table.class).name();
//        StringBuffer insertHisSql=new StringBuffer();
//        insertHisSql.append("insert into "+ HisTableName +" select * from "+tableName);
//        paramUtils = this.setWhereSql(paramUtils, insertHisSql);
//        StringBuffer insertHisReSql=new StringBuffer();
//        insertHisReSql.append("insert into IHisReport_Report (hisreportid,reqid,reportid) select '"+uuid+"',reqid,id from "+tableName);
//        newParamList = this.setWhereSql(newParamList, insertHisReSql);
//        Query nativeQuery = this.entityManager.createNativeQuery(String.valueOf(insertHisSql));
//        Query nativeQuery1 = this.entityManager.createNativeQuery(String.valueOf(insertHisReSql));
//        this.setParameterSql(paramUtils,nativeQuery);
//        this.setParameterSql(newParamList,nativeQuery1);
//        int i = nativeQuery.executeUpdate();
//        int i1 = nativeQuery1.executeUpdate();
//        if (i>0 && i1>0){
//            StringBuffer delReportSql=new StringBuffer();
//            delReportSql.append("delete "+tableName);
//            delParamList=this.setWhereSql(delParamList,delReportSql);
//            Query nativeQuery2 = this.entityManager.createNativeQuery(String.valueOf(delReportSql));
//            this.setParameterSql(delParamList,nativeQuery2);
//            int i2 = nativeQuery2.executeUpdate();
//        }
//        return i;
//    }

    /**
     * 扩展-根据多个字段查重
     * @param t 实体类
     * @param fields 字段集
     * @param paramUtils 查询条件
     * @return List<String> id集合
     */
    @Transactional(readOnly = true)
    public List<Object[]> isRepeat(T t, List<String> fields, List<ParamUtil> paramUtils){
        if (fields==null || fields.size()<=0){
            return null;
        }
        String tableName = t.getClass().getAnnotation(Table.class).name();
        StringBuffer whereField=new StringBuffer();
        fields.forEach(field->{
            whereField.append(field+"+");
        });
        whereField.deleteCharAt(whereField.length()-1);
        String whereField2=whereField.toString().replace("+",",");
        StringBuffer sql=new StringBuffer();
        sql.append("select id,reqid,name from "+tableName+" where ("+whereField+") in (select "+whereField+" from "+tableName+" ");
        paramUtils = this.setWhereSql(paramUtils, sql);
        sql.append(" GROUP BY "+whereField2+" HAVING count(1)>1)");
        Query nativeQuery = this.entityManager.createNativeQuery(String.valueOf(sql));
        this.setParameterSql(paramUtils,nativeQuery);
        List<Object[]> resultList = nativeQuery.getResultList();
        return resultList;
    }

//    /**
//     * 扩展-更新数量表
//     * @param count 数量表实体
//     * @return int
//     */
//    public int updateCount(InterfaceCount... count){
//        if (count==null || count.length==0){
//            return 0;
//        }
//        String tableName = count[0].getClass().getAnnotation(Table.class).name();
//        StringBuffer sql=new StringBuffer();
//        sql.append("update "+tableName+" set ");
//        String wsSql="";
//        String weSql="";
//        String wrSql="";
//        String hisSql="";
//        for (int i = 0; i < count.length; i++) {
//            //getId在这里用作运算符
//            String correct=null;
//            if ("add".equals(count[i].getId())){
//                correct = "+";
//            }else if ("reduce".equals(count[i].getId())){
//                correct = "-";
//            }else {
//                continue;
//            }
//            int wscount = count[i].getWscount();
//            int wecount = count[i].getWecount();
//            int wrcount = count[i].getWrcount();
//            int hiscount = count[i].getHiscount();
//            if (wscount!=0){
//                wsSql=" wscount=wscount"+correct+wscount+",";
//            }
//            if (wecount!=0){
//                weSql=" wecount=wecount"+correct+wecount+",";
//            }
//            if (wrcount!=0){
//                wrSql=" wrcount=wrcount"+correct+wrcount+",";
//            }
//            if (hiscount!=0){
//                hisSql=" hiscount=hiscount"+correct+hiscount+",";
//            }
//        }
//        if (StringUtils.isBlank(wsSql) && StringUtils.isBlank(weSql) && StringUtils.isBlank(wrSql) && StringUtils.isBlank(hisSql)){
//            return 0;
//        }
//        sql.append(wsSql+weSql+wrSql+hisSql);
//        sql.delete(sql.length()-1,sql.length());
//        //条件固定
//        sql.append(" where code=:code and orgid=:orgid and (deptid='N' or deptid=:deptid)");
//        Query query = this.entityManager.createNativeQuery(String.valueOf(sql));
//        query.setParameter("code",count[0].getCode());
//        query.setParameter("orgid",count[0].getOrgid());
//        query.setParameter("deptid",count[0].getDeptid());
//        int i = query.executeUpdate();
//        return i;
//    }

    /**
     * 扩展-保存日志
     * @param logContext 日志内容
     * @param logType 日志类型
     * @return Logs对象
     */
    public Logs saveLog(String logContext, String logType){
        Sys_UserAndOrgDepartment sys_user=((Sys_UserAndOrgDepartment) request.getSession().getAttribute("sys_User"));
        //创建Logs对象
        String uuid= UUID.randomUUID().toString().replace("-","");
        String localDateTime= LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
        Logs logs = new Logs();
        //logs.setId(uuid);
        logs.setLoginId(sys_user.getLoginid());
        logs.setLogDate(localDateTime);
        logs.setLogOrgCode(sys_user.getOrgid());
        logs.setLogContent(logContext);
        logs.setLogType(logType);
        return logsService.save(logs);
    }

    /**
     * 刷新并清空entityManager-本类使用
     */
    private void flushAndClear() {
        entityManager.flush();
        entityManager.clear();
    }

    /**
     * 拼接where条件,
     * 条件中出现两个相同字段时,改造paramList
     * @param paramList 参数集合
     * @param sql sql语句
     * @return List<ParamUtil>
     */
    private List<ParamUtil> setWhereSql(List<ParamUtil> paramList,StringBuffer sql){
        if (paramList != null && paramList.size() > 0) {
            String firstParam="";
            int count=1;
            sql.append(" where 1=1 ");
            for (ParamUtil paramUtil : paramList) {
                String field = paramUtil.getField();
                if ("sort".equals(paramUtil.getConnector())){
                    sql.append(paramUtil.where());
                }else {
                    if (field.equals(firstParam)){
                        sql.append(paramUtil.where(count));
                        paramUtil.setField(field+count);
                        count++;
                    }else {
                        sql.append(paramUtil.where());
                    }
                }
                firstParam=field;
            }
        }
        return paramList;
    }

    /**
     * 给条件赋值
     * @param paramList 参数
     * @param query Query对象
     */
    private void setParameterSql(List<ParamUtil> paramList,Query query){
        if (paramList != null && paramList.size() > 0) {
            for (ParamUtil paramUtil : paramList) {
                if ("sort".equals(paramUtil.getConnector())){
                    continue;
                }
                query.setParameter(paramUtil.getField(), paramUtil.getValue());
            }
        }
    }

    /**
     * 获取实体类赋值字段-本类使用
     * @param entity 实体类
     * @return Map<String, Object> 字段名:字段值
     */
    private Map<String, Object> setField(T entity) {
        if (entity == null) {
            return null;
        }
        Map<String, Object> map = new HashMap<>();
        Class<?> aClass = entity.getClass();
        Field[] declaredFields = aClass.getDeclaredFields();
        if (declaredFields == null || declaredFields.length <= 0) {
            return null;
        }
        for (Field declaredField : declaredFields) {
            declaredField.setAccessible(true);
            String name = declaredField.getName();
            Id annotation = declaredField.getAnnotation(Id.class);
            if (annotation != null) {
                map.put("@Id", name);
            }
            try {
                Method method = aClass.getMethod("get" + CommonStringutil.getMethodName(name));
                Object invoke = method.invoke(entity);
                if (invoke != null) {
                    map.put(name, invoke);
                }
            } catch (NoSuchMethodException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
        }
        return map;
    }

    /**
     * 删除部分提取-本类使用
     * @param sql sql语句
     * @param paramUtils 参数工具
     */
    private Query deletePart(StringBuffer sql,List<ParamUtil> paramUtils){
        paramUtils = this.setWhereSql(paramUtils, sql);
        Query query = this.entityManager.createQuery(String.valueOf(sql));
        this.setParameterSql(paramUtils,query);
        return query;
    }

    public void queryCount(Object t) {
    }
}
